﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace EntityFrameworkVisualizer.VisualizerObjectSources.CommandFormatters
{
    /// <summary>
    /// <see cref="SqlCommand"/> formatting class.
    /// </summary>
    internal class SqlCommandFormatter : DbCommandFormatter<SqlCommand>
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="SqlCommandFormatter"/> class.
        /// </summary>
        /// <param name="command">Sql command.</param>
        public SqlCommandFormatter(SqlCommand command)
            : base(command)
        {
        }

        /// <summary>
        /// Returns string representation of <see cref="SqlDbType"/>.
        /// </summary>
        /// <param name="sqlDbType"><see cref="SqlDbType"/> to format.</param>
        /// <returns>String representation of <see cref="SqlDbType"/>.</returns>
        public static string GetSqlTypeString(SqlDbType sqlDbType)
        {
            string result = null;
            switch (sqlDbType)
            {
                case SqlDbType.BigInt:
                    result = "bigint";
                    break;

                case SqlDbType.Binary:
                    result = "varbinary(max)";
                    break;

                case SqlDbType.Bit:
                    result = "bit";
                    break;

                case SqlDbType.Char:
                    result = "char(max)";
                    break;

                case SqlDbType.Date:
                    result = "date";
                    break;

                case SqlDbType.DateTime:
                    result = "datetime";
                    break;

                case SqlDbType.DateTime2:
                    result = "datetime2";
                    break;

                case SqlDbType.DateTimeOffset:
                    result = "datetimeoffset";
                    break;

                case SqlDbType.Decimal:
                    result = "decimal";
                    break;

                case SqlDbType.Float:
                    result = "float";
                    break;

                case SqlDbType.VarBinary:
                case SqlDbType.Image:
                    result = "varbinary(max)";
                    break;

                case SqlDbType.Int:
                    result = "int";
                    break;

                case SqlDbType.Money:
                    result = "money";
                    break;

                case SqlDbType.NChar:
                    result = "nchar(max)";
                    break;

                case SqlDbType.NVarChar:
                case SqlDbType.NText:
                    result = "nvarchar(max)";
                    break;

                case SqlDbType.Real:
                    result = "real";
                    break;

                case SqlDbType.SmallDateTime:
                    result = "smalldatetime";
                    break;

                case SqlDbType.SmallInt:
                    result = "smallint";
                    break;

                case SqlDbType.SmallMoney:
                    result = "smallmoney";
                    break;

                case SqlDbType.VarChar:
                case SqlDbType.Text:
                    result = "varchar(max)";
                    break;

                case SqlDbType.Time:
                    result = "time";
                    break;

                case SqlDbType.Variant:
                    result = "sql_variant";
                    break;

                case SqlDbType.Timestamp:
                    result = "timestamp";
                    break;

                case SqlDbType.TinyInt:
                    result = "tinyint";
                    break;

                case SqlDbType.UniqueIdentifier:
                    result = "uniqueidentifier";
                    break;

                case SqlDbType.Xml:
                    result = "xml";
                    break;
            }

            return result;
        }

        /// <summary>
        /// Format value of command.
        /// </summary>
        /// <param name="sqlDbType"><see cref="SqlDbType"/>.</param>
        /// <param name="value">Value of command.</param>
        /// <returns>Formatting <paramref name="value"/>.</returns>
        public static string FormatValue(SqlDbType sqlDbType, object value)
        {
            var typesToQuote = new[]
            {
                SqlDbType.Char,
                SqlDbType.Date, 
                SqlDbType.DateTime,
                SqlDbType.DateTime2,
                SqlDbType.DateTimeOffset,
                SqlDbType.NChar,
                SqlDbType.NText,
                SqlDbType.NVarChar,
                SqlDbType.SmallDateTime,
                SqlDbType.Text,
                SqlDbType.Time,
                SqlDbType.Timestamp,
                SqlDbType.UniqueIdentifier,
                SqlDbType.VarChar,
                SqlDbType.Xml
            };

            string resultValue = value.ToString();

            if (sqlDbType == SqlDbType.SmallDateTime)
            {
                resultValue = ((DateTime)value).ToString("yyyyMMdd");
            }
            else if (sqlDbType == SqlDbType.DateTime)
            {
                resultValue = ((DateTime)value).ToString("yyyy-MM-ddTHH:mm:ss.fff");
            }
            else if (sqlDbType == SqlDbType.Date)
            {
                resultValue = ((DateTime)value).ToString("yyyy-MM-dd");
            }
            else if (sqlDbType == SqlDbType.Time)
            {
                resultValue = ((DateTime)value).ToString("HH:mm:ss.fffffff");
            }
            else if (sqlDbType == SqlDbType.DateTime2)
            {
                resultValue = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fffffff");
            }
            else if (sqlDbType == SqlDbType.DateTimeOffset)
            {
                resultValue = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fffffffzzzz");
            }
            else if (value is byte[])
            {
                var array = (byte[])value;
                resultValue = "0x" + string.Concat(array.Select(b => b.ToString("X2")));
            }

            var unicode = new[] 
            { 
                SqlDbType.NChar, 
                SqlDbType.NText, 
                SqlDbType.NVarChar 
            };
            
            var prefix = unicode.Contains(sqlDbType) ? "N" : string.Empty;

            return typesToQuote.Contains(sqlDbType) ? string.Format("{1}'{0}'", resultValue, prefix) : resultValue;
        }

        /// <summary>
        /// Formatting parameters.
        /// </summary>
        /// <returns>Formatted string.</returns>
        public override string DeclareParameters()
        {
            var stringBuilder = new StringBuilder();

            foreach (var param in Command.Parameters.OfType<SqlParameter>())
            {
                stringBuilder.AppendFormat("DECLARE {0} {1} = {2};{3}", param.ParameterName, GetSqlTypeString(param.SqlDbType), FormatValue(param.SqlDbType, param.Value), Environment.NewLine);
            }

            return stringBuilder.ToString();
        }

        /// <summary>
        /// Formatting stored procedure.
        /// </summary>
        /// <returns>Formatted string.</returns>
        public override string FormatStoredProcedure()
        {
            const string CommandFormat = "EXEC {0} {1}";

            return string.Format(
                CommandFormat,
                Command.CommandText,
                string.Join(
                    ", ",
                    Command.Parameters.OfType<SqlParameter>()
                        .Select(p => string.Format("@{0} = {1}", p.ParameterName, FormatValue(p.SqlDbType, p.Value)))));
        }
    }
}
